import pandas as pd
import numpy as np
import pymysql
from sqlalchemy import create_engine


# 读取Excel文件
def read_excel_file(file_path):
    df = pd.read_excel(file_path)
    print(f"成功读取Excel文件，共{len(df)}行，{len(df.columns)}列")
    return df


# 分析数据类型并生成MySQL数据类型
def analyze_data_types(df):
    type_mapping = {
        'int64': 'INT',
        'float64': 'FLOAT',
        'object': 'VARCHAR(255)',  # 默认字符串长度
        'datetime64[ns]': 'DATE',
        'bool': 'TINYINT(1)'
    }

    columns_info = []

    for column in df.columns:
        dtype = str(df[column].dtype)
        # 获取列的统计信息
        if dtype in ['int64', 'float64']:
            min_val = df[column].min()
            max_val = df[column].max()

            # 确定合适的整数类型
            if dtype == 'int64':
                if -128 <= min_val and max_val <= 127:
                    sql_type = 'TINYINT'
                elif -32768 <= min_val and max_val <= 32767:
                    sql_type = 'SMALLINT'
                elif -2147483648 <= min_val and max_val <= 2147483647:
                    sql_type = 'INT'
                else:
                    sql_type = 'BIGINT'
            # 确定合适的浮点数类型
            else:
                # 检查是否有小数部分
                has_decimal = any(df[column].dropna() % 1 != 0)
                if has_decimal:
                    sql_type = 'FLOAT'
                else:
                    sql_type = 'INT'  # 如果没有小数，可以使用整数类型
        elif dtype == 'object':
            # 检查是否可能是日期时间
            try:
                # 尝试解析日期，指定错误处理方式
                pd.to_datetime(df[column], errors='raise')
                sql_type = 'DATE'
            except (TypeError, ValueError):
                # 计算字符串最大长度
                max_len = df[column].astype(str).str.len().max()
                # 设置合理的字符串长度
                sql_type = f'VARCHAR({max(50, int(max_len * 1.2))})'  # 增加20%的缓冲区
        elif dtype == 'datetime64[ns]':
            sql_type = 'DATETIME'
        elif dtype == 'bool':
            sql_type = 'TINYINT(1)'
        else:
            sql_type = 'VARCHAR(255)'

        columns_info.append((column, sql_type))  # 修正拼写错误

    return columns_info


# 生成创建表的SQL语句
def generate_create_table_sql(table_name, columns_info):
    sql = f"CREATE TABLE IF NOT EXISTS {table_name} (\n"
    sql += "    id INT AUTO_INCREMENT PRIMARY KEY,\n"

    for i, (column, sql_type) in enumerate(columns_info):
        # 转义列名，防止特殊字符问题
        escaped_column = column.replace('`', '``')
        sql += f"    `{escaped_column}` {sql_type}"

        if i < len(columns_info) - 1:
            sql += ",\n"
        else:
            sql += "\n"

    sql += ") ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;"
    return sql


# 创建表并导入数据
def create_table_and_import_data(df, table_name, create_table_sql, db_config):
    # 连接数据库
    conn = pymysql.connect(
        host=db_config['host'],
        user=db_config['user'],
        password=db_config['password'],
        database=db_config['database'],
        charset='utf8mb4'
    )

    try:
        # 创建表
        with conn.cursor() as cursor:
            cursor.execute(create_table_sql)
            print(f"成功创建表: {table_name}")

        # 使用SQLAlchemy引擎导入数据
        engine = create_engine(
            f"mysql+pymysql://{db_config['user']}:{db_config['password']}@{db_config['host']}/{db_config['database']}?charset=utf8mb4")

        # 处理日期列
        for column in df.columns:
            if str(df[column].dtype) == 'datetime64[ns]':
                df[column] = df[column].dt.strftime('%Y-%m-%d %H:%M:%S')

        # 将数据导入数据库
        df.to_sql(table_name, con=engine, if_exists='append', index=False)
        print(f"成功导入 {len(df)} 行数据到表: {table_name}")

    except Exception as e:
        print(f"错误: {e}")
    finally:
        conn.close()


# 主函数
def main():
    file_path = 'new_data_processed.xlsx'
    table_name = 'weather_data'
    db_config = {
        'host': 'localhost',
        'user': 'root',
        'password': '1026',
        'database': 'guilin_temp'
    }

    # 读取Excel数据
    df = read_excel_file(file_path)

    # 分析数据类型
    columns_info = analyze_data_types(df)

    # 打印列信息
    print("\n分析结果:")
    for column, sql_type in columns_info:
        print(f"{column}: {sql_type}")

    # 生成创建表的SQL语句
    create_table_sql = generate_create_table_sql(table_name, columns_info)

    # 打印SQL语句
    print("\n生成的SQL语句:")
    print(create_table_sql)

    # 确认并执行
    confirm = input("\n是否创建表并导入数据? (y/n): ")
    if confirm.lower() == 'y':
        create_table_and_import_data(df, table_name, create_table_sql, db_config)
    else:
        print("操作已取消")


if __name__ == "__main__":
    main()